﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace TextMIS
{
    class dbopr:Base
    {
        ArrayList arryList = new ArrayList();
        public int Exec_ArryList()
        {
            if (arryList.Count == 0)
            {
                return 0;
            }
            return base.SqlDBAgent.sqlupdate(arryList);
        }
        /// <summary>
        /// sql语句
        /// </summary>
        /// <param name="strSql">sql语句</param>
        public void str_Sqltxt(string strSql)
        {
            arryList.Add(strSql);
        }

        //*********************************************************人员信息****************************
        //获取科室id最大值加1后返回
        //public string Get_MaxStr(string table, string column)
        //{
        //    string strSql;
        //    strSql = "select max(cast(isnull(" + column + ",0) as int))+1 from " + table + "";
        //    return base.SqlDBAgent.sqlvalue(strSql).ToString();
        //}
        /// <summary>
        /// 获取用户列表
        /// </summary>
        /// <returns></returns>
        public DataTable Get_userlist()
        {
            string strSql;
            strSql = "select User_id,User_Name from UserInfo";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        /// <summary>
        /// 获取所有用户信息
        /// </summary>
        /// <returns></returns>
        public DataTable Get_userinfo(string userid )
        {
            string strSql;
            strSql = "select User_Id,User_Name,User_Pwd,Workgroup_Id,User_Code,User_Type,Bran_Id,IsEnable from UserInfo where User_Id=" + userid + "";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        //通过科室找到医生信息
        public DataTable Get_Branuser(string braid)
        {
            string strSql;
            strSql = "select User_Id,User_Name where Bran_Id=" + braid + "";
            return base.SqlDBAgent.GetDataTable(strSql);
        }

        /// <summary>
        /// 获取所有用户组信息
        /// </summary>
        /// <returns></returns>
        public DataTable Get_workgroup()
        {
            string strSql;
            strSql = "select wg_id,wg_name,wg_depict,Disp_order from WorkGroup order by Disp_order";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        //增加用户信息-先删除再增加  
        public int Insert_Userinfo(string id, string name, string pwd,string workgroup,string usertype,string enable)
        {
            string strSql;
            strSql = "delete UserInfo where User_Id='" + id + "'";
            strSql = strSql + "insert UserInfo(User_Id,User_Name,User_Pwd,Workgroup_Id,User_Type,IsEnable) select '" + id + "','" + name + "','"
                + pwd + "','" + workgroup + "','" + usertype + "','" + enable + "'";
            return base.SqlDBAgent.sqlupdate(strSql);
        }
        //是否存在操作员code
        public int Exist_UserCode(string usercode)
        {
            string strSql;
            strSql = "select count(1) from userinfo where user_code='" + usercode + "'";
            return base.SqlDBAgent.sqlupdate(strSql);
        }
        //删除用户信息
        public int Delete_User(string userid)
        {
            string strSql;
            strSql = "delete UserInfo where User_Id='" + userid + "'";
            return base.SqlDBAgent.sqlupdate(strSql);
        }




        

        //*******************************科室有关*************************************
        //获取科室id最大值加1后返回
        public string Get_MaxStr(string table, string column)
        {
            string strSql;
            strSql = "select max(cast(isnull(" + column + ",0) as int))+1 from " + table + "";
            return base.SqlDBAgent.sqlvalue(strSql).ToString();
        }
        //获取科室列表查
        public DataTable Get_Bran_list()
        {
            string strSql;
            strSql = "select Bra_id,Bra_name from BranchInfo order by Disp_order";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        //增加科室
        public int Insert_Bran(string id, string disp_order, string name, string type, string brifesum, string info, string bz)
        {
            string strSql;
            strSql = "insert BranchInfo(Bra_id,Disp_order,Bra_name,Bra_Type,Brife_sum,Bra_info,Bra_memo,XSLX) select '" + id + "','" + disp_order + "','" + name + "','" + type + "','" + brifesum + "','" + info + "','" + bz + "','1'";
            return base.SqlDBAgent.sqlupdate(strSql);
        }
        //删除科室
        public int Delete_Bran(string id)
        {
            string strSql;
            strSql = "delete BranchInfo where Bra_id='" + id + "'";
            return base.SqlDBAgent.sqlupdate(strSql);
        }
        //改变科室信息
        public int Update_Bran(string id, string disp_order, string name, string type, string brifesum, string info, string memo)
        {
            string strSql;
            strSql = "update BranchInfo set Disp_order='" + disp_order + "',Bra_name='" + name + "',Bra_Type='" + type + "',Brife_sum='" + brifesum + "',Bra_info='" + info + "',Bra_memo='" + memo + "' where Bra_id='" + id + "'";
            return base.SqlDBAgent.sqlupdate(strSql);
        }
        //改变前通过id先获取到所有信息-查所有
        public DataTable Get_Bran_info(string id)
        {
            string strSql;
            strSql = "select Bra_id,Disp_order,Bra_name,Bra_Type,Brife_sum,Bra_info,Bra_memo from BranchInfo where Bra_id='" + id + "'";
            return base.SqlDBAgent.GetDataTable(strSql);
        }

        
       
        //******************************什么类型表***************************************
        //
        public DataTable Get_tj_lclxb()
        {
            string strSql;
            strSql = "select lclx,mc from tj_lclxb order by DISP_ORDER";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        public DataTable Get_tj_lclxb(string lclx)
        {
            string strSql;
            strSql = "select lclx,mc,disp_order,bz from tj_lclxb where lclx='" + lclx + "'";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        public int Update_tj_lclxb(string lclx, string mc, string disp_order, string bz)
        {
            string strSql;
            strSql = "update tj_lclxb set mc='" + mc + "',disp_order='" + disp_order + "',bz='" + bz + "' where lclx='" + lclx + "'";
            return base.SqlDBAgent.sqlupdate(strSql);
        }
        public int Insert_tj_lclxb(string lclx, string mc, string disp_order, string bz)
        {
            string strSql;
            strSql = "insert tj_lclxb(lclx,mc,disp_order,bz) select '" + lclx + "','" + mc + "','" + disp_order + "','" + bz + "'";
            return base.SqlDBAgent.sqlupdate(strSql);
        }
        public int Delete_tj_lclxb(string lclx)
        {
            string strSql;
            strSql = "delete tj_lclxb where lclx='" + lclx + "'";
            return base.SqlDBAgent.sqlupdate(strSql);
        }

        //**************************体检项目表************************
        //根据 项目编号获取list
        public DataTable Get_ItemList(string id)
        {
            string strSql;
            strSql = "select Item_id,Item_name,isnull(IsEnable,0) IsEnable from ItemInfo where Bra_id='" + id + "' order by Disp_order";
            return base.SqlDBAgent.GetDataTable(strSql);
        }

        //根据 科室和临床类型 ---暂未用到
        public DataTable Get_tj_tjxmb(string lxbh, string lclx)
        {
            string strSql;
            strSql = "select tjxm,mc,isnull(qybz,0) qybz from tj_tjxmb where lxbh='" + lxbh + "' and lclx='" + lclx + "' order by DISP_ORDER";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        //根据项目编号获取项目明细-查
        public DataTable Get_Item_Info(string itemid)
        {
            string strSql;
            strSql = "select Bra_id,Item_id,Item_name,Item_Price,Disp_order,SexAttr,lclx,Item_unit,Result_Type,Result_Norm,Result_Lo,Result_Up,IsEnable,LoLimited,UpLimited from ItemInfo where Item_id='" + itemid + "'";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
  
        //增加
        //根据科室类型，获取体检项目编号 用存储过程实现
        public string Get_Pro_ItemId(string lxbh)
        {
            string strSql;
            strSql = "exec proc_get_ItemId '" + lxbh + "'";
            return base.SqlDBAgent.sqlvalue(strSql).ToString().Trim();
        }
        //Bra_id,Item_id,Item_name,dj,Disp_order,SexAttr,lclx,Item_unit,Result_Norm,Result_Lo,Result_Up,zcts,IsEnable,LoLimited,UpLimited
        public int Insert_Iteminfo(string BraId, string ItmId, string name, string dj, string disp_order, string Sex, string ResTyp, string Unit,
            string ResNom, string Enable, string lclx, string LowV, string UpV)
        {
            string strSql;
            strSql = "insert ItemInfo(Bra_id,Item_id,Item_name,Item_Price,Disp_order,SexAttr,Result_Type,Item_unit,Result_Norm,IsEnable,lclx,LoLimited,UpLimited) select '" + BraId + "','" + ItmId + "','" + name + "','" + dj
                + "','" + disp_order + "','" + Sex + "','" + ResTyp + "','" + Unit + "','" + ResNom + "','" + Enable + "','"+ lclx + "','" + LowV + "','" + UpV + "'";
            return base.SqlDBAgent.sqlupdate(strSql);
        }
        //改
        public int Update_Iteminfo(string BraId, string ItmId, string name, string price, string disp_order, string sex, string lclx, string unit,
            string ResNom, string ResTyp, string enable, string low, string uper)
        {
            string strSql;
            strSql = "update ItemInfo set Bra_id='" + BraId + "',Item_name='" + name + "',Item_Price='" + price + "',Disp_order='" + disp_order + "',SexAttr='" + sex + "',lclx='" + lclx
                + "',Item_unit='" + unit + "',Result_Norm='" + ResNom + "',Result_Type='" + ResTyp + "',IsEnable='" + enable + "',LoLimited='" + low + "',UpLimited='" + uper + "' where Item_id='" + ItmId + "'";
            return base.SqlDBAgent.sqlupdate(strSql);
        }
        //删
        public int Delete_Iteminfo(string ItmId)
        {
            string strSql;
            strSql = "delete ItemInfo where Item_id='" + ItmId + "'";
            return base.SqlDBAgent.sqlupdate(strSql);
        }

        //组合项目列表 编号系统选项中存放最大值
        //**************************************************
        //通过科室获取组合项目列表，用于Treelist显示
        public DataTable Get_GroupItm_list(string Bra_id)
        {
            string strSql;
            strSql = "select Gro_id,Gro_name,isnull(IsEnable,0) IsEnable from GrpItem where Bra_id='" + Bra_id + "' order by Disp_Order";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        public DataTable Get_tj_zhxm_hd(string tjlx, string lclx)
        {
            string strSql;
            strSql = "select bh,mc,isnull(yxbz,0) yxbz from tj_zhxm_hd where tjlx='" + tjlx + "' and lclx='" + lclx + "' order by DISP_ORDER";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        //获取分组项目详细信息
        public DataTable Get_GrpItm_Info(string bh)
        {
            string strSql;
            strSql = "select Bra_id,IsEnable,Gro_id,Disp_Order,Gro_name,dj,lclx,DefCode,Pinyin_code,Wubi_code,Mid_Basis,TipInfo,Brife_sum,IsMuter,MuterTyp,Check_Type,Fees_type from GrpItem where Gro_id='" + bh + "'";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        //修改组合项目
        public int Update_tj_zhxm_hd(string tjlx, string yxbz, string bh, string disp_order, string mc, string dj, string lclx, string zdym,
            string pyjm, string wbjm, string lcyy, string tsxx, string zcxj, string sfxybb, string bblx, string jcjylx, string sflb)
        {
            string strSql;
            strSql = "update tj_zhxm_hd set tjlx='" + tjlx + "',yxbz='" + yxbz + "',disp_order='" + disp_order + "',mc='" + mc + "',dj='" + dj + "',lclx='" + lclx
                + "',zdym='" + zdym + "',pyjm='" + pyjm + "',wbjm='" + wbjm + "',lcyy='" + lcyy + "',tsxx='" + tsxx + "',zcxj='" + zcxj + "',sfxybb='" + sfxybb
                + "',bblx='" + bblx + "',jcjylx='" + jcjylx + "',sflb='" + sflb + "' where bh='" + bh + "'";
            return base.SqlDBAgent.sqlupdate(strSql);
        }

        //增加组合项目明细
        public int Insert_GrpItmInfo(string tjlx, string yxbz, string bh, string disp_order, string mc, string dj, string lclx, string zdym,
            string pyjm, string wbjm, string lcyy, string tsxx, string zcxj, string sfxybb, string bblx, string jcjylx, string sflb)
        {
            string strSql;
            strSql = "insert GrpItem(Bra_id,IsEnable,Gro_id,Disp_Order,Gro_name,dj,lclx,DefCode,Pinyin_code,Wubi_code,Mid_Basis,TipInfo,Brife_sum,IsMuter,MuterTyp,Check_Type,Fees_type,SFCX,GrpPrice,SFCQ) select '" +
                tjlx + "','" + yxbz + "','" + bh + "','" + disp_order + "','" + mc + "','" + dj + "','" + lclx + "','" + zdym + "','" +
                pyjm + "','" + wbjm + "','" + lcyy + "','" + tsxx + "','" + zcxj + "','" + sfxybb + "','" + bblx + "','" + jcjylx + "','" + sflb + "','1','" + dj + "','1'";
            return base.SqlDBAgent.sqlupdate(strSql);
        }
        //删除组合项目明细grpitm及其关联grpitemrel
        public int Delete_GroupItm(string id)//明细表一起删除
        {
            string strSql;
            strSql = "delete GrpItem where Gro_id='" + id + "';";
            strSql = strSql + "delete GrpItemRel where Grp_Id='" + id + "'";
            return base.SqlDBAgent.sqlupdate(strSql);
        }
        //根据科室ID获取项目
        public DataTable Get_Iteminfo_Bybar(string lxbh, string lclx)
        {
            string strSql;
            //strSql = "select a.Bra_id,a.Item_id,b.xmmc xb from ItemInfo a join xt_zdxm b on a.xb=b.bzdm where b.zdflbm=1 and a.lxbh='" + lxbh + "' and isnull(lclx,'')='" + lclx + "' and a.qybz=1 order by a.disp_order";
            strSql = "select a.Item_id ,a.Item_name,b.xmmc xb from ItemInfo a join xt_zdxm b on a.SexAttr=b.bzdm where b.zdflbm=1  and Bra_id='" + lxbh + "' and isnull(lclx,'')='" + lclx + "' and IsEnable=1 order by disp_order";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        //获取分组关系表 用于DG显示 
        public DataTable Get_GrpItem_Rel(string bh)
        {
            string strSql;
            strSql = "select Itm_Id from GrpItemRel where Grp_Id='" + bh + "'";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        //存储分组关系表 GrpItemRel
        public void str_Insert_GrpItemRel(string bh, string tjlx, string tjxm, string flag)
        {
            string strSql;
            strSql = "insert GrpItemRel(Grp_Id,Bra_Id,Itm_Id,Flag) select '" + bh + "','" + tjlx + "','" + tjxm + "','" + flag + "'";
            arryList.Add(strSql);
        }
        //根据科室类型，获取体检组合项目编号
        public string Get_proc_get_tjzhxmbh(string lxbh)
        {
            string strSql;
            strSql = "exec proc_get_tjzhxmbh '" + lxbh + "'";
            return base.SqlDBAgent.sqlvalue(strSql).ToString().Trim();
        }



        //**************************************************
        public DataTable Get_TJ_SUGGESTION()
        {
            string strSql;
            strSql = "select '' bh,'' keyword union select bh,keyword from TJ_SUGGESTION";
            return base.SqlDBAgent.GetDataTable(strSql);
        }

        public DataTable Get_TJ_SUGGESTION(string tjlx, string lclx)
        {
            string strSql;
            strSql = "select '' bh,'' keyword union select bh,keyword from TJ_SUGGESTION where tjlx='" + tjlx + "' and isnull(lclx,'')='" + lclx + "'";
            return base.SqlDBAgent.GetDataTable(strSql);
        }

        public DataTable Get_TJ_SUGGESTION1(string tjlx, string lclx)
        {
            string strSql;
            strSql = "select '' bh,'' mc,'' keyword,'' pyjm,'' wbjm union select a.bh,b.mc,a.keyword,a.pyjm,a.wbjm from TJ_SUGGESTION a join tj_tjlxb b on a.tjlx=b.lxbh where a.tjlx='" + tjlx + "' and isnull(a.lclx,'')='" + lclx + "'";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        public DataTable Get_TJ_SUGGESTION2()
        {
            string strSql;
            strSql = "select '' bh,'' mc,'' keyword,'' pyjm,'' wbjm union select a.bh,b.mc,a.keyword,a.pyjm,a.wbjm from TJ_SUGGESTION a join tj_tjlxb b on a.tjlx=b.lxbh";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        public DataTable Get_TJ_SUGGESTION3(string tjlx)
        {
            string strSql;
            strSql = "select '' bh,'' mc,'' keyword,'' pyjm,'' wbjm union select a.bh,b.mc,a.keyword,a.pyjm,a.wbjm from TJ_SUGGESTION a join tj_tjlxb b on a.tjlx=b.lxbh where a.tjlx='" + tjlx + "'";
            return base.SqlDBAgent.GetDataTable(strSql);
        }

        public DataTable Get_TJ_TJXMB_XJ(string tjxm)//结果，项目是否小结
        {
            string strSql;
            strSql = "select lxbh,isnull(lclx,'') lclx,tjxm,sfxj,mcjrxj,xb,jglx from TJ_TJXMB where tjxm='" + tjxm + "'";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        public DataTable Get_tj_keyword_zf(string tjxm)//字符类型
        {
            string strSql;
            strSql = "select bh,mc,keyword,isnull(into_xj,0) into_xj,isnull(mcjrxj,0) mcjrxj,isnull(sfyx,0) sfyx,pyjm,wbjm,jglx,xb,tjlx,tjxm from tj_keyword where tjxm='" + tjxm + "' and jglx='00' order by bh";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        public DataTable Get_tj_keyword_sz_all(string tjxm)//数字类型，男女共有
        {
            string strSql;
            strSql = "select bh,nlxx,nlsx,dy,spy,xy,xpy,mc,isnull(sfzc,0) sfzc,keyword,isnull(mcjrxj,0) mcjrxj,isnull(into_xj,0) into_xj,isnull(sfyx,0) sfyx,pyjm,wbjm,jglx,xb,tjlx,tjxm,whys from tj_keyword where tjxm='" + tjxm + "' and jglx='20' order by bh";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        public DataTable Get_tj_keyword_sz_nx(string tjxm)//数字类型，男有
        {
            string strSql;
            strSql = "select bh,nlxx,nlsx,dy,spy,xy,xpy,mc,isnull(sfzc,0) sfzc,keyword,isnull(mcjrxj,0) mcjrxj,isnull(into_xj,0) into_xj,isnull(sfyx,0) sfyx,pyjm,wbjm,jglx,xb,tjlx,tjxm,whys from tj_keyword where tjxm='" + tjxm + "' and jglx='11' order by bh";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        public DataTable Get_tj_keyword_sz_vx(string tjxm)//数字类型，女有
        {
            string strSql;
            strSql = "select bh,nlxx,nlsx,dy,spy,xy,xpy,mc,isnull(sfzc,0) sfzc,keyword,isnull(mcjrxj,0) mcjrxj,isnull(into_xj,0) into_xj,isnull(sfyx,0) sfyx,pyjm,wbjm,jglx,xb,tjlx,tjxm,whys from tj_keyword where tjxm='" + tjxm + "' and jglx='10' order by bh";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        public void str_Insert_tj_keyword_zf(string bh, string mc, string keyword, string into_xj, string mcjrxj, string sfyx, string pyjm, string wbjm,
            string jglx, string xb, string tjlx, string tjxm)
        {
            string strSql;
            strSql = "insert tj_keyword(bh,mc,keyword,into_xj,mcjrxj,sfyx,pyjm,wbjm,jglx,xb,tjlx,tjxm) select '" + bh + "','" + mc + "','" + keyword + "','" + into_xj
                + "','" + mcjrxj + "','" + sfyx + "','" + pyjm + "','" + wbjm + "','" + jglx + "','" + xb + "','" + tjlx + "','" + tjxm + "'";
            arryList.Add(strSql);
        }
        public void str_Insert_tj_keyword_sz(string bh, string nlxx, string nlsx, string dy, string xy, string mc, string sfzc, string keyword,
            string mcjrxj, string into_xj, string sfyx, string pyjm, string wbjm, string jglx, string xb, string tjlx, string tjxm, string spy, string xpy, string whys)
        {
            string strSql;
            strSql = "insert tj_keyword(bh,nlxx,nlsx,dy,xy,mc,sfzc,keyword,mcjrxj,into_xj,sfyx,pyjm,wbjm,jglx,xb,tjlx,tjxm,spy,xpy,whys) select '" + bh + "','" + nlxx + "','" + nlsx + "','" + dy
                + "','" + xy + "','" + mc + "','" + sfzc + "','" + keyword + "','" + mcjrxj + "','" + into_xj + "','" + sfyx + "','" + pyjm + "','" + wbjm + "','" + jglx + "','" + xb
                + "','" + tjlx + "','" + tjxm + "','" + spy + "','" + xpy + "','" + whys + "'";
            arryList.Add(strSql);
        }
        public void str_Update_tj_tjxmb(string lxbh, string tjxm, string sfxj, string mcjrxj)
        {
            string strSql;
            strSql = "update TJ_TJXMB set sfxj='" + sfxj + "',mcjrxj='" + mcjrxj + "' where lxbh='" + lxbh + "' and tjxm='" + tjxm + "'";
            arryList.Add(strSql);
        }

        public string Get_tj_suggestion_jbbh(string tjlx, string keyword)
        {
            string strSql;
            strSql = "select bh from tj_suggestion where tjlx='" + tjlx + "' and ltrim(rtrim(keyword))='" + keyword + "'";
            try
            {
                return base.SqlDBAgent.sqlvalue(strSql).ToString().Trim();
            }
            catch
            {
                return "";
            }
        }

        //public void str_Update_tj_tjxmb2(string lxbh, string tjxm, string ckxx, string cksx, string pdts, string pgts)
        //{
        //    string strSql;
        //    strSql = "update TJ_TJXMB set ckxx='" + ckxx + "',cksx='" + cksx + "',pdts='" + pdts + "',pgts='" + pgts + "' where lxbh='" + lxbh + "' and tjxm='" + tjxm + "'";
        //    arryList.Add(strSql);
        //}

        //**************************************************
        public DataTable Get_TJ_SUGGESTION_All(string tjlx, string lclx)//
        {
            string strSql;
            strSql = "select bh,keyword,mc,jbbh,sfcjb,jynr,explain,pyjm,wbjm,zdym,icd,tjlx,isnull(lclx,'') lclx from TJ_SUGGESTION where tjlx='" + tjlx + "' and isnull(lclx,'')='" + lclx + "' order by keyword";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        public int Delete_TJ_SUGGESTION(string bh, string tjlx, string lclx)
        {
            string strSql;
            strSql = "delete TJ_SUGGESTION where bh='" + bh + "' and tjlx='" + tjlx + "' and isnull(lclx,'')='" + lclx + "'";
            strSql = strSql + ";update TJ_KEYWORD set keyword=null where keyword='" + bh + "' and tjlx='" + tjlx + "'";//2012-08-17吴：删除诊断建议的同时，删除常见结果和该项的关联
            return base.SqlDBAgent.sqlupdate(strSql);
        }
        public int Insert_TJ_SUGGESTION(string bh, string keyword, string mc, string jbbh, string sfcjb, string jynr, string explain, string pyjm,
            string wbjm, string zdym, string icd, string tjlx, string lclx)
        {
            string strSql;
            strSql = "delete TJ_SUGGESTION where bh='" + bh + "' and tjlx='" + tjlx + "'";
            strSql = strSql + "insert TJ_SUGGESTION(bh,keyword,mc,jbbh,sfcjb,jynr,explain,pyjm,wbjm,zdym,icd,tjlx,lclx) select '" + bh + "','" + keyword + "','" + mc + "','" + jbbh
                + "','" + sfcjb + "','" + jynr + "','" + explain + "','" + pyjm + "','" + wbjm + "','" + zdym + "','" + icd + "','" + tjlx + "','" + lclx + "'";
            return base.SqlDBAgent.sqlupdate(strSql);
        }


        //**********************套餐表操作****************************
        public DataTable Get_PackList()  //获取套餐信息
        {
            string strSql;
            strSql = "select Pack_id,Pack_name from PackageItem order by cast(Disp_order as int)";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        public DataTable Get_PackItem(string Pakid) //根据套餐id获取套餐详细信息
        {
            string strSql;
            strSql = "select Pack_id,Disp_order,SexAttr,Pack_name,Pinyin_sam,StandPrice,SalePrice,BuisType,Pack_memo from PackageItem where Pack_id='" + Pakid + "'";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        public DataTable Get_PackItemRel(string packid)// 获取套餐项目关系表
        {
            string strSql;
            //strSql = "select a.zhxm,b.mc from tj_tc_dt a join tj_zhxm_hd b on a.zhxm=b.bh where a.tcbh='" + tcbh + "' order by b.disp_order";
            strSql= "select a.Grp_id,b.Gro_name from PackageItemRel a join GrpItem b on a.Grp_id=b.Gro_id where a.Pack_id='" + packid + "' order by b.Disp_Order";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        public void str_Delete_PackList(string Packid)  //删除套餐内容
        {
            string strSql;
            strSql = "delete PackageItem where Pack_id='" + Packid + "'";
            arryList.Add(strSql);
        }
        public void str_Delete_PackItem(string Packid)  //删除套餐项目
        {
            string strSql;
            strSql = "delete PackageItem where Pack_id='" + Packid + "'";
            arryList.Add(strSql);
        }

        //新增体检套餐内容
        public void str_Insert_PackItem(string packid, string disp_order, string sex, string name, string shortname, string standprice, string price, string BisTyp, string memo)
        {
            string strSql;   
            strSql = "delete PackageItemRel where Pack_id='" + packid + "';";
            strSql = strSql + "insert PackageItem(Pack_id,Disp_order,SexAttr,Pack_name,Pinyin_sam,StandPrice,SalePrice,BuisType,Pack_memo) select '" + packid + "','" + disp_order + "','" + sex + "','" + name
                + "','" + shortname + "','" + standprice + "','" + price + "','" + BisTyp + "','" + memo + "'";
            arryList.Add(strSql);
        }
        //管理套餐和组合项目关系  先删除再增加
        public void str_Insert_PackItemRel(string packid, string groid)
        {
            string strSql;
            
            strSql ="insert PackageItemRel(Pack_id,Grp_id,Flag) select '" + packid + "','" + groid + "','1'";
            arryList.Add(strSql);
        }
        //组合项目详情--
        public DataTable Get_GrpItm_Info()
        {
            string strSql;
            strSql = "select Bra_id,IsEnable,Gro_id,Disp_Order,Gro_name,dj,lclx,DefCode,Pinyin_code,Wubi_code,Mid_Basis,TipInfo,Brife_sum,IsMuter,MuterTyp,Check_Type,Fees_type from GrpItem ";
            return base.SqlDBAgent.GetDataTable(strSql);
        }

        //public DataTable Get_GrpInfo()
        //{
        //    string strSql;
        //    strSql = "select bh,mc,tjlx,pyjm,wbjm,zdym from tj_zhxm_hd where yxbz=1 order by tjlx,disp_order";
        //    return base.SqlDBAgent.GetDataTable(strSql);
        //}

        public string Get_ZhxmJg(string bh)//获取所选项目的标准价格
        {
            string strSql;
            strSql = "select sum(isnull(GrpPrice,0)) jg from GrpItem where 1=1 and Gro_id in" + bh;
            return base.SqlDBAgent.sqlvalue(strSql).ToString();
        }
        //***************************************************************************************
        //处理数据被引用，不能被删除

        public int Exists_Lclx(string lclx)
        {
            string strSql;
            strSql = "select count(1) from tj_tjxmb where lclx='" + lclx + "'";
            return Convert.ToInt32(base.SqlDBAgent.sqlvalue(strSql));
        }
        //科室是否被项目引用
        public int Exists_BraBy(string id)
        {
            string strSql;
            strSql = "select count(1) from ItemInfo where Bra_id='" + id + "'";
            return Convert.ToInt32(base.SqlDBAgent.sqlvalue(strSql));
        }
        public int Exists_Tjxm(string tjxm)
        {
            string strSql;
            strSql = "select count(1) from tj_zhxm_dt where tjxm='" + tjxm + "'";
            return Convert.ToInt32(base.SqlDBAgent.sqlvalue(strSql));
        }
        public int Exists_Tjzhxm(string tjzhxm)
        {
            string strSql;
            strSql = "select count(1) from tj_tjjlb where tjxmbh='" + tjzhxm + "'";
            return Convert.ToInt32(base.SqlDBAgent.sqlvalue(strSql));
        }
        //***************************************************************************************
        public DataTable Get_tj_sqdlx_hd()
        {
            string strSql;
            strSql = "select flbh,flmc from tj_sqdlx_hd order by disp_order";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        public DataTable Get_tj_sqdlx_hd(string flbh)
        {
            string strSql;
            strSql = "select flbh,disp_order,flmc,jcjylx,bblx,bz from tj_sqdlx_hd where flbh='" + flbh + "'";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        public DataTable Get_tj_zhxm_hd(string flbh, string tjlx, string lclx)
        {
            string strSql;
            strSql = "select bh,mc,tjlx from tj_zhxm_hd where tjlx='" + tjlx + "' and isnull(lclx,'')='" + lclx + "' and bh not in(select bh from tj_sqdlx_dt) order by disp_order";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        public int Delete_tj_sqdlx_hd(string flbh)
        {
            string strSql;
            strSql = "delete tj_sqdlx_hd where flbh='" + flbh + "';";
            strSql = strSql + "delete tj_sqdlx_dt where flbh='" + flbh + "'";
            return Convert.ToInt32(base.SqlDBAgent.sqlvalue(strSql));
        }
        public void str_Insert_tj_sqdlx_hd(string flbh, string disp_order, string flmc, string jcjylx, string bblx, string bz)
        {
            string strSql;
            strSql = "delete tj_sqdlx_hd where flbh='" + flbh + "';";
            strSql = strSql + "insert tj_sqdlx_hd(flbh,disp_order,flmc,jcjylx,bblx,bz) select '" + flbh + "','" + disp_order + "','" + flmc + "','" + jcjylx
                + "','" + bblx + "','" + bz + "'";
            arryList.Add(strSql);
        }

        public int Delete_tj_sqdlx_dt(string flbh, string bh, string xh, string tjlx)
        {
            string strSql;
            strSql = "delete tj_sqdlx_dt where flbh='" + flbh + "' and bh='" + bh + "' and xh='" + xh + "' and tjlx='" + tjlx + "'";
            return base.SqlDBAgent.sqlupdate(strSql);
        }
        public int Insert_tj_sqdlx_dt(string flbh, string bh, string xh, string tjlx)
        {
            string strSql;
            strSql = "insert tj_sqdlx_dt(flbh,bh,xh,tjlx) select '" + flbh + "','" + bh + "','" + xh + "','" + tjlx + "'";
            return base.SqlDBAgent.sqlupdate(strSql);
        }
        public DataTable Get_tj_sqdlx_dt(string flbh)
        {
            string strSql;
            strSql = "select a.bh,b.mc,a.flbh,a.xh,a.tjlx from tj_sqdlx_dt a join tj_zhxm_hd b on a.bh=b.bh where a.flbh='" + flbh + "' order by b.disp_order";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        public string Get_MaxXh_tj_sqdlx_dt(string flbh)
        {
            string strSql;
            strSql = "select isnull(max(xh),0)+1 from tj_sqdlx_dt where flbh='" + flbh + "'";
            return base.SqlDBAgent.sqlvalue(strSql).ToString();
        }
        //*******************************************************************************
        public DataTable Get_tj_jbmb_hd()
        {
            string strSql;
            strSql = "select bh,mbmc from tj_jbmb_hd order by disp_order";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        public DataTable Get_tj_jbmb_hd(string bh)
        {
            string strSql;
            strSql = "select bh,disp_order,mbmc,bz from tj_jbmb_hd where bh='" + bh + "'";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        public DataTable Get_tj_jbmb_dt(string bh)
        {
            string strSql;
            strSql = "select a.bh,b.keyword,a.xh,a.jbbh,a.tjlx from tj_jbmb_dt a join tj_suggestion b on a.jbbh=b.bh where a.bh='" + bh + "' order by b.bh";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        public DataTable Get_tj_suggestion(string tjlx, string bh)
        {
            string strSql;
            strSql = "select bh jbbh,keyword,tjlx from tj_suggestion where tjlx='" + tjlx + "' and jbbh='1' and bh not in(select jbbh from tj_jbmb_dt where bh='" + bh + "') order by bh";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        public int Insert_tj_jbmb_dt(string bh, string xh, string jbbh, string tjlx)
        {
            string strSql;
            strSql = "insert tj_jbmb_dt(bh,xh,jbbh,tjlx) select '" + bh + "','" + xh + "','" + jbbh + "','" + tjlx + "'";
            return base.SqlDBAgent.sqlupdate(strSql);
        }
        public string Get_MaxXh_tj_jbmb_dt(string bh)
        {
            string strSql;
            strSql = "select isnull(max(xh),0)+1 from tj_jbmb_dt where bh='" + bh + "'";
            return base.SqlDBAgent.sqlvalue(strSql).ToString();
        }
        public int Delete_tj_jbmb_dt(string bh, string xh, string jbbh, string tjlx)
        {
            string strSql;
            strSql = "delete tj_jbmb_dt where bh='" + bh + "' and xh='" + xh + "' and jbbh='" + jbbh + "' and tjlx='" + tjlx + "'";
            return base.SqlDBAgent.sqlupdate(strSql);
        }
        public int Delete_tj_jbmb_hd(string bh)
        {
            string strSql;
            strSql = "delete tj_jbmb_hd where bh='" + bh + "';";
            strSql = strSql + "delete tj_jbmb_dt where bh='" + bh + "'";
            return Convert.ToInt32(base.SqlDBAgent.sqlvalue(strSql));
        }
        public void str_Insert_tj_jbmb_hd(string bh, string disp_order, string mbmc, string bz)
        {
            string strSql;
            strSql = "delete tj_jbmb_hd where bh='" + bh + "';";
            strSql = strSql + "insert tj_jbmb_hd(bh,disp_order,mbmc,bz) select '" + bh + "','" + disp_order + "','" + mbmc + "','" + bz + "'";
            arryList.Add(strSql);
        }
        //*******************************************************************************
        public DataTable Get_tj_hsb_hd()
        {
            string strSql;
            strSql = "select bh,zhxmbh,mc,ms from tj_hsb_hd order by bh";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        public DataTable Get_tj_hsb_xmdz(string hsid)
        {
            string strSql;
            strSql = "select mc,dzm,bz,hsid,gjz from tj_hsb_xmdz where hsid='" + hsid + "' order by disp_order";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        public DataTable Get_tj_hsb_dt(string hsid)
        {
            string strSql;
            strSql = "select xh,mc,ckz,zdbh,bh,bz from tj_hsb_dt where bh='" + hsid + "' order by xh";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        public int Update_tj_hsb_hd(string bh, string zhxmbh, string ms)
        {
            string strSql;
            strSql = "update tj_hsb_hd set zhxmbh='" + zhxmbh + "',ms='" + ms + "' where bh='" + bh + "'";
            return base.SqlDBAgent.sqlupdate(strSql);
        }
        public DataTable Update_tj_hsb_xmdz(DataTable objDataTable)
        {
            string strSql;
            strSql = "select mc,dzm,bz,hsid,gjz from tj_hsb_xmdz";
            return base.SqlDBAgent.Update(objDataTable, strSql);
        }
        public DataTable Update_tj_hsb_dt(DataTable objDataTable)
        {
            string strSql;
            strSql = "select xh,mc,ckz,zdbh,bh,bz from tj_hsb_dt";
            return base.SqlDBAgent.Update(objDataTable, strSql);
        }
        //*******************************************************************************
        public DataTable Get_TJ_JBFB_NL()
        {
            string strSql;
            strSql = "select xh,mc,begin_age,end_age from TJ_JBFB_NL";
            return base.SqlDBAgent.GetDataTable(strSql);
        }
        public DataTable Update_TJ_JBFB_NL(DataTable objDataTable)
        {
            string strSql;
            strSql = "select xh,mc,begin_age,end_age from TJ_JBFB_NL";
            return base.SqlDBAgent.Update(objDataTable, strSql);
        }


        public void str_Delete_tjyw()
        {
            string strSql;
            strSql = "truncate table  dbo.TJ_SFB;truncate table  dbo.TJ_TFB;truncate table  dbo.TJ_TJDJB;truncate table  dbo.TJ_TJJLB;truncate table  dbo.TJ_TJJLMXB;";
            arryList.Add(strSql);
        }


    }
}
